<?php

namespace app\admin\command;

use PDO;
use think\Config;
use think\console\Command;
use think\console\Input;
use think\console\input\Option;
use think\console\Output;
use think\Db;
use think\Exception;

class Channel extends Command
{
    protected $model = null;

    protected function configure()
    {
        $config = Config::get('database');
        $this
            ->setName('channel')
            ->setDescription('channel statistics');
    }

    protected function execute(Input $input, Output $output)
    {
        $statistics_date = date('Y-m-d', strtotime('-1 day'));
        $start_time      = strtotime($statistics_date);
        $end_time        = $start_time + 86400;

        $results = [];
        $channels = Db::name('channel')->column('channel_name', 'channel_code');

        $news = Db::name('cpa')->where('login_time', '>=', $start_time)->where('login_time', '<', $end_time)->where('is_new', 1)->group('channel_code')->column('COUNT(1)', 'channel_code');

        $registers = Db::name('user_loginlog')->where('logintime', '>=', $start_time)->where('logintime', '<', $end_time)->where('is_new', 1)->group('channel_code')->column('COUNT(1)', 'channel_code');

        $actives = Db::name('cpa')->where('login_time', '>=', $start_time)->where('login_time', '<', $end_time)->group('channel_code')->column('COUNT(1)', 'channel_code');

        $ips = Db::name('cpa')->where('login_time', '>=', $start_time)->where('login_time', '<', $end_time)->group('channel_code')->column('count(DISTINCT(ip))', 'channel_code');

        $login_actives = Db::name('user_loginlog')->where('logintime', '>=', $start_time)->where('logintime', '<', $end_time)->group('channel_code')->column('COUNT(1)', 'channel_code');

        $starts = Db::name('cpa')->where('login_time', '>=', $start_time)->where('login_time', '<', $end_time)->group('channel_code')->column('COUNT(1)', 'channel_code');

        $hits = Db::name('statistics')->where('createtime', '>=', $start_time)->where('createtime', '<', $end_time)->group('channel_code')->column('COUNT(1)', 'channel_code');

        $i = 0;
        foreach ($channels as $key => $val) {
            $results[$i]['date']               = $statistics_date;
            $results[$i]['channel_code']       = $key;
            $results[$i]['new_num']            = $news[$key] ?? 0;
            $results[$i]['register_num']       = $register_nums[$key] ?? 0;
            $results[$i]['register_rate']      = 0 > $results[$i]['new_num'] ? round($register_nums[$i] / $results[$i]['new_num'], 4) * 100: 0;
            $results[$i]['active_num']         = $actives[$key] ?? 0;
            $results[$i]['not_new_active_num'] = $results[$i]['active_num'] - $results[$i]['new_num'];
            $results[$i]['ip_num']             = $ips[$key] ?? 0;
            $results[$i]['login_active_num']   = $login_actives[$key] ?? 0;
            $results[$i]['start_num']          = $starts[$key] ?? 0;
            $results[$i]['hit_num']            = $hits[$key] ?? 0;

            if (Db::name('channelstatistics')->where('date', $statistics_date)->where('channel_code', $key)->count()) {
                Db::name('channelstatistics')->where('date', $statistics_date)->where('channel_code', $key)->update($results[$i]);
            } else {
                Db::name('channelstatistics')->insert($results[$i]);
            }
            $i++;
        }
        
        $output->info("Statistics Successed!");
    }
}
